import pandas as pd
import my_jdbc as jdbc
import json
import run_request_service as res
from datetime import datetime

if __name__ == '__main__':
    file_name_path = 'D:\\tx_data\\em\\出差补打卡 - 副本(5).xlsx'
    data = pd.read_excel(file_name_path, sheet_name='Sheet1', usecols=['日', '时间', '工号', '名字'])
    data.dropna(subset=['日', '时间', '工号', '名字'], inplace=True)

    save_clockin = """INSERT INTO `kcdoc`.`kc_user_clockin` 
                      (`user_no`, `user_name`, `seat`, `lat`, `lon`, `clockin_time`, `image_id`, `msg`, `type`) 
                      VALUES (%s, %s, '补卡', 30.7226600, 103.9575040, %s, '', NULL, '1');"""
    save_data = []
    for index, row in data.iterrows():
        combined_str = row['日'] + ' ' + row['时间']
        formatted_str = datetime.strptime(combined_str, "%Y/%m/%d %H:%M:%S").strftime("%Y-%m-%d %H:%M:%S")
        save_data.append((row['工号'], row['名字'], formatted_str))

    jdbc.app_add_data(save_clockin, save_data)
